import pymysql


class MySQLHelper:
    def __init__(self, config):
        self.host = config["host"]
        self.port = config["port"]
        self.user = config["user"]
        self.password = config["password"]
        self.db = config["db"]
        self.charset = config["charset"]
        self.con = None
        self.cursor = None

    def create_con(self):
        '''创建数据库连接'''
        try:
            self.con = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password,
                                       database = self.db, charset = self.charset)
            self.cursor = self.con.cursor()
            return True
        except Exception as e:
            print(str(e))
            return False

    def close_con(self):
        '''关闭连接'''
        if self.cursor:
            self.cursor.close()
        if self.con:
            self.con.close()

    def update(self, sql, *args):
        '''执行插入、更新、删除语句'''
        try:
            self.create_con()
            self.cursor.execute(sql, args)
            self.con.commit() # 事务提交
        except Exception as e:
            print(str(e))
        finally:
            self.close_con()

    def addBatch(self,sql,dataList):
        '''执行批量插入、更新、删除语句'''
        try:
            self.create_con()
            self.cursor.executemany(sql, dataList)
            self.con.commit()
        except Exception as e:
            print(str(e))
        finally:
            self.close_con()

    def query(self,sql, *args, size=1):
        '''执行查询语句'''
        try:
            self.create_con()
            self.cursor.execute(sql, args)
            if (size<=0):
                return self.cursor.fetchall()
            elif (size == 1):
                return self.cursor.fetchone()
            else:
                return self.cursor.fetchmany(size)
        except Exception as e:
            print(str(e))
            return False
        finally:
            self.close_con()
